--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';




DELETE FROM  se_PAR_UNFAB ;




INSERT INTO se_PAR_UNFAB
 (SELECT id unfab_cod,
         nom unfab_lib,
         SubStr(cp || ' ' || ville,0,30) unfab_lib2,
         0 enreg_id,
         'N' enreg_s,
         '01' soc_cod,
         '01' ets_cod,
         langue traduction_cod,
         NULL renvunf_cod,
         NULL renvunfnom_cod,
         '9099' renvunfnominex_cod,
         null renvunfgam_cod,
         '9099' renvunfgaminex_cod,
         NULL renvunffrais_cod,
         NULL depotate_cod
    FROM uv_pfournisseurs fuv
    WHERE strait='O' OR depot IS NOT NULL
      OR EXISTS (SELECT 1 FROM uv_pnomartent_uv n WHERE n.unfab_cod=fuv.id) );

 UPDATE se_PAR_UNFAB tf
 SET enreg_id = (select enreg_id FROM &erp.PAR_UNFAB f WHERE f.unfab_cod=tf.unfab_cod )
 WHERE EXISTS (SELECT 1 FROM &erp.PAR_UNFAB f WHERE f.unfab_cod=tf.unfab_cod);

 UPDATE se_PAR_UNFAB SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;


 CALL uvse_synchroverserp('unfab_cod','PAR_UNFAB','O') ;

COMMIT;

SELECT &outils.stragg(source) source,unfab_cod, unfab_lib, unfab_lib2, enreg_id, enreg_s, soc_cod, ets_cod, traduction_cod, renvunf_cod, renvunfnom_cod, renvunfnominex_cod, renvunfgam_cod, renvunfgaminex_cod, renvunffrais_cod, depotate_cod
FROM (
  SELECT 'uv' source ,g.* FROM se_PAR_UNFAB g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.PAR_UNFAB g
  )
WHERE unfab_cod !='ETUDE'
GROUP BY unfab_cod, unfab_lib, unfab_lib2, enreg_id, enreg_s, soc_cod, ets_cod, traduction_cod, renvunf_cod, renvunfnom_cod, renvunfnominex_cod, renvunfgam_cod, renvunfgaminex_cod, renvunffrais_cod, depotate_cod
HAVING Count(1)!=2
ORDER BY unfab_cod;